﻿Imports VSModel
Imports Cls_Common
Imports System.IO
Imports SecurityService
Partial Class Control_Reports_BaoCaoHangThang
    Inherits System.Web.UI.UserControl
    Private Shared ReadOnly log As log4net.ILog = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType)
    Dim level As String = -1

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim script As ScriptManager = ScriptManager.GetCurrent(Me.Page)
            If Not script Is Nothing AndAlso script.IsInAsyncPostBack Then
                ScriptManager.RegisterStartupScript(Me.Page, Me.Page.GetType, "regjs", "ajaxJquery()", True)
            Else
                Me.Page.ClientScript.RegisterStartupScript(Me.Page.GetType(), "regjs", String.Concat("Sys.Application.add_load(function(){", "ajaxJquery()", "});"), True)
            End If
            hidCurProvince.Value = Session("HuyenId")
        End If
    End Sub
#Region "Sub and Function"
    Private Sub BindToGrid(Optional ByVal dtmDateFrom As Date = #1/1/1900# _
                           , Optional ByVal dtmDateTo As Date = #12/31/3999# _
                           )
        Dim intHuyen As Integer = hidCurProvince.Value
        Using _data As New VSEntities
            Dim arrSearch() As String = {dtmDateFrom, dtmDateTo, intHuyen.ToString}
            ViewState("search") = arrSearch
        End Using
    End Sub
#End Region
    Protected Sub btnReset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnReset.Click
        txtTuNgayFrom.Text = ""
        txtToiNgayTo.Text = ""
    End Sub
    Protected Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnExportToExcel.Click
        Dim DateFrom As DateTime = IIf(txtTuNgayFrom.Text.Trim <> "", StringToDate(txtTuNgayFrom.Text.Trim), "1900/01/01")
        Dim DateTo As DateTime = IIf(txtToiNgayTo.Text.Trim <> "", StringToDate(txtToiNgayTo.Text.Trim), "3999/12/31")

        Dim intHuyen As Integer = hidCurProvince.Value
        BindToGrid(DateFrom, DateTo)
        Try

            Dim arrSearch() As String = {DateFrom, DateTo, intHuyen.ToString}
            ViewState("search") = arrSearch
            arrSearch = ViewState("search")
            Using data As New VSEntities
                Dim _fileName As String = "ReportHangThang_" & Date.Now.ToFileTimeUtc()
                Dim p As List(Of uspCertifiedBaoCaoHangThang_Result) = data.uspCertifiedBaoCaoHangThang(arrSearch(0), arrSearch(1), arrSearch(2)).ToList
                Dim applicationPath As String = Server.MapPath(Request.ApplicationPath)
                If Not applicationPath.EndsWith("\") Then
                    applicationPath = applicationPath + "\"
                End If
                Dim licenseFile As String = Path.Combine(applicationPath, "bin\Aspose.Cells.lic")
                Dim license As Aspose.Cells.License = New Aspose.Cells.License()
                license.SetLicense(licenseFile)
                Dim _workbook As New Aspose.Cells.Workbook(Server.MapPath("~/Template/ReportHangThang.xls"))
                Dim _workSheet As Aspose.Cells.Worksheet = _workbook.Worksheets(0)

                '_workSheet.Cells.ImportCustomObjects(CType(p, System.Collections.ICollection), _fieldName, False, 9, 8, p.Count, True, "dd/MM/yyyy", False)
                'Adding a string value to the cell
                'huyện báo cáo
                Dim _Huyen = (From q In data.Provinces
                              Where q.Id = hidCurProvince.Value Select q.Title).FirstOrDefault
                If Not _Huyen Is Nothing Then
                    Dim _H = "UBND " & (_Huyen.ToUpper)
                    _workSheet.Cells("A1").PutValue(_H)
                End If
                'ngày xuất báo cáo
                Dim _T As String = Now.Month
                Dim _N As String = Now.Year
                'If ddlMonth.SelectedValue.Length < 2 Then
                '    _M = "0" & CType(ddlMonth.SelectedValue, String)
                'Else
                '    _M = ddlMonth.SelectedValue
                'End If
                Dim _NgayXuatBaoCao = "Tháng " & _T & " năm " & _N
                _workSheet.Cells("A6").PutValue(_NgayXuatBaoCao)
                Dim _N1 = "Tháng " & _T & "/" & _N
                _workSheet.Cells("I8").PutValue(_N1)
                'ngày xuất báo cáo
                Dim _D As String
                If CType(Now.Day, String).Length < 2 Then
                    _D = "0" & CType(Now.Day, String)
                Else
                    _D = CType(Now.Day, String)
                End If

                Dim _T1 As String
                If CType(Now.Month, String).Length < 2 Then
                    _T1 = "0" & CType(Now.Month, String)
                Else
                    _T1 = CType(Now.Month, String)
                End If
                Dim _N2 = _Huyen.Replace("huyện", "") & ", Ngày " & _D & " tháng " & _T1 & " năm " & Now.Year
                _workSheet.Cells("F39").PutValue(_N2)
                'số giấy chứng nhận
                Dim _TTGiayChungNhan As String = (From q In p Where q.Ten = "TTGiayChungNhan" Select q.GiaTri).FirstOrDefault
                If Not _TTGiayChungNhan Is Nothing Then
                    _workSheet.Cells("I10").PutValue(_TTGiayChungNhan)
                End If
                'số giấy chứng nhận luy Ke
                Dim _TTGiayChungNhanLuyKe As String = (From q In p Where q.Ten = "TTGiayChungNhanLuyKe" Select q.GiaTri).FirstOrDefault
                If Not _TTGiayChungNhanLuyKe Is Nothing Then
                    _workSheet.Cells("J10").PutValue(_TTGiayChungNhanLuyKe)
                End If
                'số giấy chứng nhận hộ
                Dim _TTGiayChungNhanHo As String = (From q In p Where q.Ten = "TTGiayChungNhan" Select q.GiaTri).FirstOrDefault
                If Not _TTGiayChungNhanHo Is Nothing Then
                    _workSheet.Cells("I11").PutValue(_TTGiayChungNhanHo)
                End If
                'số giấy chứng nhận luy Ke
                Dim _TTGiayChungNhanLuyKeHo As String = (From q In p Where q.Ten = "TTGiayChungNhanLuyKe" Select q.GiaTri).FirstOrDefault
                If Not _TTGiayChungNhanLuyKeHo Is Nothing Then
                    _workSheet.Cells("J11").PutValue(_TTGiayChungNhanLuyKeHo)
                End If
                'tổng vốn đăng ký theo tháng
                Dim _TTVonDangKy As String = (From q In p Where q.Ten = "TTVonDangKy" Select q.GiaTri).FirstOrDefault
                If _TTVonDangKy <> "0" And _TTVonDangKy <> "" Then
                    _workSheet.Cells("I14").PutValue(CType(_TTVonDangKy / 1000000, Double))
                End If
                'tổng vốn đăng ký theo tháng luy tuyền
                Dim _TTVonDangKyLuyKe As String = (From q In p Where q.Ten = "TTVonDangKyLuyKe" Select q.GiaTri).FirstOrDefault
                If _TTVonDangKyLuyKe <> "0" And _TTVonDangKyLuyKe <> "" Then
                    _workSheet.Cells("J14").PutValue(CType(_TTVonDangKyLuyKe / 1000000, Double))
                End If
                'tổng vốn đăng ký theo tháng hộ
                Dim _TTVonDangKyHo As String = (From q In p Where q.Ten = "TTVonDangKy" Select q.GiaTri).FirstOrDefault
                If _TTVonDangKyHo <> "0" And _TTVonDangKyHo <> "" Then
                    _workSheet.Cells("I15").PutValue(CType(_TTVonDangKyHo / 1000000, Double))
                End If
                'tổng vốn đăng ký theo tháng luy tuyền
                Dim _TTVonDangKyLuyKeHo As String = (From q In p Where q.Ten = "TTVonDangKyLuyKe" Select q.GiaTri).FirstOrDefault
                If _TTVonDangKyLuyKeHo <> "0" And _TTVonDangKyLuyKeHo <> "" Then
                    _workSheet.Cells("J15").PutValue(CType(_TTVonDangKyLuyKeHo / 1000000, Double))
                End If
                'đăng ký thay đổi
                Dim _TTThayDoi As String = (From q In p Where q.Ten = "TTThayDoi" Select q.GiaTri).FirstOrDefault
                If Not _TTThayDoi Is Nothing Then
                    _workSheet.Cells("I18").PutValue(_TTThayDoi)
                End If
                'đăng ký thay đổi luy Ke
                Dim _TTThayDoiLuyKe As String = (From q In p Where q.Ten = "TTThayDoiLuyKe" Select q.GiaTri).FirstOrDefault
                If Not _TTThayDoiLuyKe Is Nothing Then
                    _workSheet.Cells("J18").PutValue(_TTThayDoiLuyKe)
                End If
                'ngành nghề kinh doanh thay đổi
                Dim _TTNganhNghe As String = (From q In p Where q.Ten = "TTNganhNghe" Select q.GiaTri).FirstOrDefault
                If Not _TTNganhNghe Is Nothing Then
                    _workSheet.Cells("I19").PutValue(_TTNganhNghe)
                End If
                'ngành nghề kinh doanh thay đổi luy Ke
                Dim _TTNganhNgheLuyKe As String = (From q In p Where q.Ten = "TTNganhNgheLuyKe" Select q.GiaTri).FirstOrDefault
                If Not _TTNganhNgheLuyKe Is Nothing Then
                    _workSheet.Cells("J19").PutValue(_TTNganhNgheLuyKe)
                End If
                'dịa chỉ trụ sở thay đổi
                Dim _TTTruSo As String = (From q In p Where q.Ten = "TTTruSo" Select q.GiaTri).FirstOrDefault
                If Not _TTTruSo Is Nothing Then
                    _workSheet.Cells("I20").PutValue(_TTTruSo)
                End If
                'dịa chỉ trụ sở thay đổi luy Ke
                Dim _TTTruSoLuyKe As String = (From q In p Where q.Ten = "TTTruSoLuyKe" Select q.GiaTri).FirstOrDefault
                If Not _TTTruSoLuyKe Is Nothing Then
                    _workSheet.Cells("J20").PutValue(_TTTruSoLuyKe)
                End If
                'hộ kinh doanh thay đổi
                Dim _TTDoanhNghiep As String = (From q In p Where q.Ten = "TTDoanhNghiep" Select q.GiaTri).FirstOrDefault
                If Not _TTDoanhNghiep Is Nothing Then
                    _workSheet.Cells("I21").PutValue(_TTDoanhNghiep)
                End If
                'hộ kinh doanh thay đổi
                Dim _TTDoanhNghiepLuyKe As String = (From q In p Where q.Ten = "TTDoanhNghiepLuyKe" Select q.GiaTri).FirstOrDefault
                If Not _TTDoanhNghiepLuyKe Is Nothing Then
                    _workSheet.Cells("J21").PutValue(_TTDoanhNghiepLuyKe)
                End If
                'đại diện kinh doanh thay đổi
                Dim _TTDaiDien As String = (From q In p Where q.Ten = "TTDaiDien" Select q.GiaTri).FirstOrDefault
                If Not _TTDaiDien Is Nothing Then
                    _workSheet.Cells("I22").PutValue(_TTDaiDien)
                End If
                'đại diện kinh doanh thay đổi
                Dim _TTDaiDienLuyKe As String = (From q In p Where q.Ten = "TTDaiDienLuyKe" Select q.GiaTri).FirstOrDefault
                If Not _TTDaiDienLuyKe Is Nothing Then
                    _workSheet.Cells("J22").PutValue(_TTDaiDienLuyKe)
                End If
                'thay đổi vốn đăng ký hộ
                Dim _TTDoiVon As String = (From q In p Where q.Ten = "TTDoiVon" Select q.GiaTri).FirstOrDefault
                If Not _TTDoiVon Is Nothing Then
                    _workSheet.Cells("I23").PutValue(_TTDoiVon)
                End If
                'thay đổi vốn đăng ký hộ luy Ke
                Dim _TTDoiVonLuyKe As String = (From q In p Where q.Ten = "TTDoiVonLuyKe" Select q.GiaTri).FirstOrDefault
                If Not _TTDoiVonLuyKe Is Nothing Then
                    _workSheet.Cells("J23").PutValue(_TTDoiVonLuyKe)
                End If
                'tăng vốn hộ kinh doanh
                Dim _TTHoTangVon As String = (From q In p Where q.Ten = "TTHoTangVon" Select q.GiaTri).FirstOrDefault
                If Not _TTHoTangVon Is Nothing Then
                    _workSheet.Cells("I24").PutValue(_TTHoTangVon)
                End If
                'tăng vốn hộ kinh doanh luy Ke
                Dim _TTHoTangVonLuyKe As String = (From q In p Where q.Ten = "TTHoTangVonLuyKe" Select q.GiaTri).FirstOrDefault
                If Not _TTHoTangVonLuyKe Is Nothing Then
                    _workSheet.Cells("J24").PutValue(_TTHoTangVonLuyKe)
                End If
                'số vốn tăng hộ kinh doanh
                Dim _TTSoVonTang As String = (From q In p Where q.Ten = "TTSoVonTang" Select q.GiaTri).FirstOrDefault
                If Not _TTSoVonTang Is Nothing Then
                    If CType(_TTSoVonTang, Double) < 0 Then
                        _workSheet.Cells("I25").PutValue("0")
                    Else
                        _workSheet.Cells("I25").PutValue(CType(_TTSoVonTang / 1000000, Double))
                    End If
                Else
                    _workSheet.Cells("I25").PutValue("0")
                End If
                'số vốn tăng hộ kinh doanh lũy Ke
                Dim _TTSoVonTangLuyKe As String = (From q In p Where q.Ten = "TTSoVonTangLuyKe" Select q.GiaTri).FirstOrDefault
                If Not _TTSoVonTangLuyKe Is Nothing Then
                    If CType(_TTSoVonTangLuyKe, Double) < 0 Then
                        _workSheet.Cells("J25").PutValue("0")
                    Else
                        _workSheet.Cells("J25").PutValue(CType(_TTSoVonTangLuyKe / 1000000, Double))
                    End If
                Else
                    _workSheet.Cells("J25").PutValue("0")
                End If
                'giam vốn hộ kinh doanh
                Dim _TTHoGiamVon As String = (From q In p Where q.Ten = "TTHoGiamVon" Select q.GiaTri).FirstOrDefault
                If Not _TTHoGiamVon Is Nothing Then
                    _workSheet.Cells("I26").PutValue(_TTHoGiamVon)
                End If
                'giam vốn hộ kinh doanh lũy Ke
                Dim _TTHoGiamVonLuyKe As String = (From q In p Where q.Ten = "TTHoGiamVonLuyKe" Select q.GiaTri).FirstOrDefault
                If Not _TTHoGiamVonLuyKe Is Nothing Then
                    _workSheet.Cells("J26").PutValue(_TTHoGiamVonLuyKe)
                End If
                'số vốn giam hộ kinh doanh
                Dim _TTSoVonGiam As String = (From q In p Where q.Ten = "TTSoVonGiam" Select q.GiaTri).FirstOrDefault
                If Not _TTSoVonGiam Is Nothing Then
                    If CType(_TTSoVonGiam, Double) < 0 Then
                        _workSheet.Cells("I27").PutValue("0")
                    Else
                        _workSheet.Cells("I27").PutValue(CType(_TTSoVonGiam / 1000000, Double))
                    End If
                Else
                    _workSheet.Cells("I27").PutValue("0")
                End If
                'số vốn giam hộ kinh doanh lũy Ke
                Dim _TTSoVonGiamLuyKe As String = (From q In p Where q.Ten = "TTSoVonGiamLuyKe" Select q.GiaTri).FirstOrDefault
                If Not _TTSoVonGiamLuyKe Is Nothing Then
                    If CType(_TTSoVonGiamLuyKe, Double) < 0 Then
                        _workSheet.Cells("J27").PutValue("0")
                    Else
                        _workSheet.Cells("J27").PutValue(CType(_TTSoVonGiamLuyKe / 1000000, Double))
                    End If
                Else
                    _workSheet.Cells("J27").PutValue("0")
                End If
                'hộ tam ngừng kinh doanh
                Dim _TTHoTamNgung As String = (From q In p Where q.Ten = "TTHoTamNgung" Select q.GiaTri).FirstOrDefault
                If Not _TTHoTamNgung Is Nothing Then
                    _workSheet.Cells("I28").PutValue(_TTHoTamNgung)
                End If
                'hộ tam ngừng kinh doanh lũy Ke
                Dim _TTHoTamNgungLuyKe As String = (From q In p Where q.Ten = "TTHoTamNgungLuyKe" Select q.GiaTri).FirstOrDefault
                If Not _TTHoTamNgungLuyKe Is Nothing Then
                    _workSheet.Cells("J28").PutValue(_TTHoTamNgungLuyKe)
                End If

                _workSheet.AutoFitColumns()
                Cls_Common.CreateDir(Server.MapPath("~/Template/ReportDownload/"))
                _workbook.Save(Server.MapPath("~/Template/ReportDownload/" & _fileName & ".xls"))
                Excute_Javascript("Alertbox('Xuất dữ liệu thành công'); SetDownFile('" & ResolveUrl("~/Template/ReportDownload/" & _fileName & ".xls") & "')", Me.Page, True)
            End Using
        Catch ex As Exception
            log4net.Config.XmlConfigurator.Configure()
            log.Error("Error error " & AddTabSpace(1) & Session("Username") & AddTabSpace(1) & "IP:" & GetIPAddress(), ex)
            Excute_Javascript("Alertbox('Xuất dữ liệu thất bại." & ex.Message & "');", Me.Page, True)
        End Try
    End Sub
End Class
